﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlServerCe;
using System.Configuration;
using System.Collections;

namespace SWEN3Application.Classes
{
    public class RoomDBManager
    {
        public static Boolean createRoom(Rooms r)
        {
            SqlCeConnection conn = null;
            Boolean IsSuccess=false;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Rooms(RoomNo,RoomStatus,RoomType,RoomRates)"
                + " VALUES (@RoomNo,@RoomStatus,@RoomType,@RoomRates)";

                comm.Parameters.AddWithValue("@RoomNo", r.RoomNo);
                comm.Parameters.AddWithValue("@RoomStatus", r.RoomStatus);
                comm.Parameters.AddWithValue("@RoomType", r.RoomType);
                comm.Parameters.AddWithValue("@RoomRates", r.RoomRates);

                if (comm.ExecuteNonQuery() > 0)
                {
                    IsSuccess = true;
                }
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return IsSuccess;
        }

        public static ArrayList GetAllRooms()
        {
            ArrayList result = new ArrayList();
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Rooms";
                SqlCeDataReader dr = comm.ExecuteReader();
                DateTime Default = DateTime.Parse("1/1/2001");
                while (dr.Read())
                {
                    Rooms r = new Rooms();
                    r.RoomNo = (int)dr["RoomNo"];
                    r.RoomRates = (double)dr["RoomRates"];
                    r.RoomStatus = (String)dr["RoomStatus"];
                    r.RoomType = (String)dr["RoomType"];
                    r.CheckIn = (DateTime)(dr["CheckIn"] != DBNull.Value ? dr["CheckIn"] : Default);
                    r.CheckOut = (DateTime)(dr["CheckOut"] != DBNull.Value ? dr["CheckOut"] : Default);
                    r.BookingNo = (int)(dr["BookingNo"] != DBNull.Value ? dr["BookingNo"] : 0);
                    r.StaffId = (int)(dr["StaffId"] != DBNull.Value ? dr["StaffId"] : 0);
                    result.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        public static Rooms GetRoomByStatus(string RoomStatus)
        {
            SqlCeConnection conn = null;
            Rooms r = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Rooms WHERE RoomStatus = @RoomStatus";
                comm.Parameters.AddWithValue("@RoomStatus", RoomStatus);
                SqlCeDataReader dr = comm.ExecuteReader();
                DateTime Default = DateTime.Parse("1/1/2001");

                if (dr.Read())
                {
                    r = new Rooms();
                    r.RoomNo = (int)dr["RoomNo"];
                    r.RoomRates = (double)dr["RoomRates"];
                    r.RoomStatus = (String)dr["RoomStatus"];
                    r.RoomType = (String)dr["RoomType"];
                    r.CheckIn = (DateTime)(dr["CheckIn"] != DBNull.Value ? dr["CheckIn"] : Default);
                    r.CheckOut = (DateTime)(dr["CheckOut"] != DBNull.Value ? dr["CheckOut"] : Default);
                    r.BookingNo = (int)(dr["BookingNo"] != DBNull.Value ? dr["BookingNo"] : 0);
                    r.StaffId = (int)(dr["StaffId"] != DBNull.Value ? dr["StaffId"] : 0);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return r;
        }

        public static Rooms GetRoomByBookingNo(int BookingNo)
        {
            SqlCeConnection conn = null;
            Rooms r = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Rooms WHERE BookingNo = @BookingNo";
                comm.Parameters.AddWithValue("@BookingNo", BookingNo);
                SqlCeDataReader dr = comm.ExecuteReader();
                DateTime Default = DateTime.Parse("1/1/2001");

                if (dr.Read())
                {
                    r = new Rooms();
                    r.RoomNo = (int)dr["RoomNo"];
                    r.BookingNo=(int)dr["BookingNo"];
                    r.RoomRates = (double)dr["RoomRates"];
                    r.RoomStatus = (String)dr["RoomStatus"];
                    r.RoomType = (String)dr["RoomType"];
                    r.CheckIn = (DateTime)(dr["CheckIn"] != DBNull.Value ? dr["CheckIn"] : Default);
                    r.CheckOut = (DateTime)(dr["CheckOut"] != DBNull.Value ? dr["CheckOut"] : Default);
                    r.BookingNo = (int)(dr["BookingNo"] != DBNull.Value ? dr["BookingNo"] : 0);
                    r.StaffId = (int)(dr["StaffId"] != DBNull.Value ? dr["StaffId"] : 0);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return r;
        }

        public static Rooms GetRoomByRoomNo(int RoomNo)
        {
            SqlCeConnection conn = null;
            Rooms r = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Rooms WHERE RoomNo = @RoomNo";
                comm.Parameters.AddWithValue("@RoomNo", RoomNo);
                SqlCeDataReader dr = comm.ExecuteReader();
                DateTime Default = DateTime.Parse("1/1/2001");

                if (dr.Read())
                {
                    r = new Rooms();
                    r.RoomNo = (int)dr["RoomNo"];
                    r.RoomRates = (double)dr["RoomRates"];
                    r.RoomStatus = (String)dr["RoomStatus"];
                    r.RoomType = (String)dr["RoomType"];
                    r.CheckIn = (DateTime)(dr["CheckIn"] != DBNull.Value ? dr["CheckIn"] : Default);
                    r.CheckOut = (DateTime)(dr["CheckOut"] != DBNull.Value ? dr["CheckOut"] : Default);
                    r.BookingNo = (int)(dr["BookingNo"] != DBNull.Value ? dr["BookingNo"] : 0);
                    r.StaffId = (int)(dr["StaffId"] != DBNull.Value ? dr["StaffId"] : 0);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return r;
        }
        public static Rooms GetRoomByDate(DateTime CheckIn, DateTime CheckOut)//DateTime CheckIn2
        {
            SqlCeConnection conn = null;
            Rooms r = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Rooms WHERE CheckIn>= @CheckIn and CheckOut >= @CheckOut";//between @CheckIn and @CheckIn";
                comm.Parameters.AddWithValue("@CheckIn", CheckIn);
                //comm.Parameters.AddWithValue("@CheckIn", CheckIn2);
                comm.Parameters.AddWithValue("@CheckOut", CheckOut);
                SqlCeDataReader dr = comm.ExecuteReader();
                DateTime Default = DateTime.Parse("1/1/2001");
                //RoomStatus = 'Occupied' and  and CheckOut <= @ CheckOut"
                if (dr.Read())
                {
                    r = new Rooms();
                    r.RoomNo = (int)dr["RoomNo"];
                    r.RoomRates = (double)dr["RoomRates"];
                    r.RoomStatus = (String)dr["RoomStatus"];
                    r.RoomType = (String)dr["RoomType"];
                    r.CheckIn = (DateTime)(dr["CheckIn"] != DBNull.Value ? dr["CheckIn"] : Default);
                    r.CheckOut = (DateTime)(dr["CheckOut"] != DBNull.Value ? dr["CheckOut"] : Default);
                    r.BookingNo = (int)(dr["BookingNo"] != DBNull.Value ? dr["BookingNo"] : 0);
                    r.StaffId = (int)(dr["StaffId"] != DBNull.Value ? dr["StaffId"] : 0);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return r;
        }

        public static Boolean updateRooms(Rooms r)
        {
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Rooms SET RoomType=@RoomType,RoomStatus=@RoomStatus, RoomRates=@RoomRates, CheckIn=@CheckIn, CheckOut=@CheckOut, BookingNo=@BookingNo, StaffId=@StaffId WHERE RoomNo=@RoomNo";
                comm.Parameters.AddWithValue("@RoomNo", r.RoomNo);
                comm.Parameters.AddWithValue("@RoomType", r.RoomType);
                comm.Parameters.AddWithValue("@RoomStatus", r.RoomStatus);
                comm.Parameters.AddWithValue("@RoomRates", r.RoomRates);

                if (r.BookingNo == 0)
                {
                    comm.Parameters.AddWithValue("@BookingNo", DBNull.Value);
                }
                else
                {
                    comm.Parameters.AddWithValue("@BookingNo", r.BookingNo);
                }
                if (r.StaffId == 0)
                {
                    comm.Parameters.AddWithValue("@StaffId", DBNull.Value);
                }
                else
                {
                    comm.Parameters.AddWithValue("@StaffId", r.StaffId);
                }
                if (r.CheckIn == DateTime.MinValue)
                {
                    comm.Parameters.AddWithValue("@CheckIn", DBNull.Value);
                }
                else
                {
                    comm.Parameters.AddWithValue("@CheckIn", r.CheckIn);
                }
                if (r.CheckOut == DateTime.MinValue)
                {
                    comm.Parameters.AddWithValue("@CheckOut", DBNull.Value);
                }
                else
                {
                    comm.Parameters.AddWithValue("@CheckOut", r.CheckOut);
                }
                if (comm.ExecuteNonQuery() == 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}